import os
import time

import openpyxl

class ExcelHandler:
    def __init__(self,file):
        self.file = file

    def get_sheetnames(self,file_path):
        xls = openpyxl.load_workbook(file_path)
        sheet_names = xls.sheetnames
        sheet_names_list =[]
        for i in sheet_names:
            sheet_names_list.append(i)
        return sheet_names_list

    def open_excel(self,sheet_name):
        ''' 打开excel 、获取sheet'''
        wb = openpyxl.load_workbook(self.file)

        # 获取sheet_name
        sheet = wb[sheet_name]

        return sheet
    # 读取表头
    def get_header(self,sheet_name):
        ''' 获取header(表头)'''
        wb = self.open_excel(sheet_name)
        header =[]

        # 遍历第一行
        for i in wb[1]:
            # 将遍历出来的表头加入列表
            header.append(i.value)
        return header

    # 读取所有数据
    def read_excel(self,sheet_name):
        ''' 读取所有数据'''
        sheet = self.open_excel(sheet_name)

        # 获取sheet的行数
        rows =list(sheet.rows)
        data =[]
        # 从第二行开始遍历每一行
        for row in rows[1:]:
            row_data =[]
            # 遍历每一行的每一个单元格
            for cell in row:
                # 将每一个单元格的值存放到列表中
                row_data.append(cell.value)
                # 重点来了 通过zip函数将两个列表合成字典
                data_dict =dict(zip(self.get_header(sheet_name),row_data))
                # print(data_dict)
            data.append(data_dict)
        # print(data)
        return data

    @staticmethod
    def write_excel(file,sheet_name,row,cloum,data):
        ''' Excel写入数据'''
        wb  =openpyxl.load_workbook(file)
        sheet =wb[sheet_name]
        sheet.cell(row,cloum).value = data
        wb.save(file)
        # print('数据写入成功')
        wb.close()

class get_all_excel_path:

    def get_all_test_excel_names(self,test_excel_cata_logue_path):
        test_excel_cata_logue_path = test_excel_cata_logue_path
        excel_names = os.listdir(test_excel_cata_logue_path)
        excel_name_path_dict = {}
        # 进行路径拼接
        for excel_name in excel_names:
            excel_name_path = test_excel_cata_logue_path + '/' + str('{}'.format(excel_name))
            excel_name_path_dict[excel_name] = excel_name_path

        return excel_name_path_dict


if __name__ == '__main__':
    # 调试的代码
    # excel_name_path = '../excel_cases/plcase1.xlsx'
    # data = ExcelHandler(excel_name_path).read_excel('wms_login')
    # print(data)
    sheet_name ='wms_login'
    file= '/config/wms_login.xlsx'
    update_time = time.strftime('%Y/%m/%d %H:%M:%S', time.localtime(time.time()))  # 获取当前时间并格式化
    ExcelHandler(file).write_excel(file=file,sheet_name=sheet_name,data=update_time,row=2,cloum=13)